if exists (select 1
            from  sysobjects
           where  id = object_id('view_bd_schedule')
            and   type = 'V')
   drop view view_bd_schedule
go

/*==============================================================*/
/* View: view_bd_schedule                                       */
/*==============================================================*/
create view view_bd_schedule as
SELECT
	b.user_no,
	b.user_serial,
	d.dep_serial as dept_serial,
	d.dep_name as dept_name,
	a.rq,
	a.year,
	a.month,
	b.user_lname AS user_name,
	isnull( a.d1, '' ) d1,
	isnull( a.d2, '' ) d2,
	isnull( a.d3, '' ) d3,
	isnull( a.d4, '' ) d4,
	isnull( a.d5, '' ) d5,
	isnull( a.d6, '' ) d6,
	isnull( a.d7, '' ) d7,
	isnull( a.d8, '' ) d8,
	isnull( a.d9, '' ) d9,
	isnull( a.d10, '' ) d10,
	isnull( a.d11, '' ) d11,
	isnull( a.d12, '' ) d12,
	isnull( a.d13, '' ) d13,
	isnull( a.d14, '' ) d14,
	isnull( a.d15, '' ) d15,
	isnull( a.d16, '' ) d16,
	isnull( a.d17, '' ) d17,
	isnull( a.d18, '' ) d18,
	isnull( a.d19, '' ) d19,
	isnull( a.d20, '' ) d20,
	isnull( a.d21, '' ) d21,
	isnull( a.d22, '' ) d22,
	isnull( a.d23, '' ) d23,
	isnull( a.d24, '' ) d24,
	isnull( a.d25, '' ) d25,
	isnull( a.d26, '' ) d26,
	isnull( a.d27, '' ) d27,
	isnull( a.d28, '' ) d28,
	isnull( a.d29, '' ) d29,
	isnull( a.d30, '' ) d30,
	isnull( a.d31, '' ) d31,
	isnull(c.user_lname,'系统排班') AS gly_name,
	convert(varchar(19),e.update_time,120) as update_time
FROM
	kt_paiba a
	INNER JOIN dt_user b ON a.user_serial= b.user_serial
	LEFT JOIN dt_user c ON a.gly_no= CONVERT ( VARCHAR ( 50 ), c.user_serial )
	LEFT JOIN dt_dep d on b.user_dep = d.dep_serial
	left join (
		select x.user_serial,x.rq,max(x.update_time) as update_time from (
			select user_serial,convert(varchar(7),shift_date,120) as rq, update_time from  bd_kq_schedule_detail) x
			GROUP BY x.user_serial,x.rq
	) e on e.user_serial = a.user_serial and e.rq = a.rq
WHERE
	( d.dep_no IS NOT NULL )
	AND ( b.user_type<= 50 )
go
